﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Collections;
using System.Data.SqlClient;

namespace WikcLib.Data
{
    public abstract class SqlHelper
    {
        public static readonly string strConnectionString = System.Configuration.ConfigurationSettings.AppSettings["sqlConn"].ToString();//Data Source=localhost;Catalog=FoodMart 2008";

        #region 执行对数据库的操作指令 (不返回结果集)
        /// <summary>
        /// 执行对数据库的操作指令 (不返回结果集)
        /// </summary>
        /// <remarks>
        /// 如：
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">对于一个SqlConnection有效的连接字符串</param>
        /// <param name="commandType">命令类型(stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程名字或者T-SQL语句</param>
        /// <param name="commandParameters">一系列SQL参数</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {

            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 执行对数据库的操作指令 (不返回结果集)
        /// </summary>
        /// <remarks>
        /// 如：
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">现有的数据库连接</param>
        /// <param name="commandType">命令类型(stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程名字或者T-SQL语句</param>
        /// <param name="commandParameters">一系列SQL参数</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {

            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// 执行对数据库的操作指令 (不返回结果集)
        /// </summary>
        /// <remarks>
        /// 如：
        ///  int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="trans">现有的SQL事务</param>
        /// <param name="commandType">命令类型 (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程名字或者T-SQL语句</param>
        /// <param name="commandParameters">一系列SQL参数</param>
        /// <returns>返回受影响行数</returns>
        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }
        #endregion

        #region 执行操作返回SqlDataReader结果集
        /// <summary>
        /// 执行SQL命令，并返回SqlDataReader结果集
        /// </summary>
        /// <remarks>
        /// 如：
        ///  SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">对于一个SqlConnection有效的连接字符串</param>
        /// <param name="commandType">命令类型 (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程名字或者T-SQL语句</param>
        /// <param name="commandParameters">一系列SQL参数</param>
        /// <returns>返回一个SqlDataReader结果集</returns>
        public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connectionString);

            // 我们在这里使用try/ catch语句，因为如果方法抛出一个异常，
            // 我们需要手动关闭数据库连接
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }
        #endregion

        #region 执行操作返回DataSet
        /// <summary>
        /// 执行操作返回DataSet
        /// </summary>
        /// <param name="connectionString">对于一个SqlConnection有效的连接字符串</param>
        /// <param name="commandType">命令类型 (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程名字或者T-SQL语句</param>
        /// <param name="commandParameters">一系列SQL参数</param>
        /// <returns>返回一个DataSet结果集</returns>
        public static DataSet ExecuteDataSet(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandTimeout = 3600;
            SqlConnection conn = new SqlConnection(connectionString);

            try
            {
                DataSet ds = new DataSet();
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);
                sqlDa.Fill(ds);
                conn.Close();
                return ds;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }
        #endregion

        #region 执行操作返回第一行的第一列
        /// <summary>
        /// 执行命令返回第一行的第一列内容
        /// </summary>
        /// <remarks>
        /// 如：
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">对于一个SqlConnection有效的连接字符串</param>
        /// <param name="commandType">命令类型 (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程名字或者T-SQL语句</param>
        /// <param name="commandParameters">一系列SQL参数</param>
        /// <returns>返回一个对象，其可以通过Convert.To{Type}转换成想要的类型</returns>
        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 执行命令返回第一行的第一列内容
        /// </summary>
        /// <remarks>
        /// 如：
        ///  Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">现有的数据库连接</param>
        /// <param name="commandType">命令类型 (stored procedure, text, etc.)</param>
        /// <param name="commandText">存储过程名字或者T-SQL语句</param>
        /// <param name="commandParameters">一系列SQL参数</param>
        /// <returns>返回一个对象，其可以通过Convert.To{Type}转换成想要的类型</returns>
        public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {

            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }
        #endregion

        #region 为执行命令做准备
        /// <summary>
        /// 为执行命令做准备
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <param name="conn">SqlConnection对象</param>
        /// <param name="trans">SqlTransaction对象</param>
        /// <param name="cmdType">命令类型(如：stored procedure or text)</param>
        /// <param name="cmdText">命令语句(如： Select * from Products)</param>
        /// <param name="cmdParms">参数列表</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
        #endregion

        /// <summary>
        /// Copy DataTable到数据库
        /// </summary>
        /// <param name="dtSource">数据库</param>
        /// <param name="strTableName">目标表名称</param>
        /// <param name="strConn">连接字符串</param>
        /// <returns></returns>
        public static Boolean CopyTable(DataTable dtSource, string strTableName, string strConn)
        {
            try
            {
                Boolean IsOk = false;

                SqlBulkCopy sbc = new SqlBulkCopy(strConn);
                sbc.BulkCopyTimeout = 3600;
                sbc.DestinationTableName = strTableName;
                sbc.WriteToServer(dtSource);
                IsOk = true;

                return IsOk;
            }
            catch (Exception)
            { 
                throw;
            }
        }


    }
}
